from django.core.management.base import BaseCommand
from django.conf import settings
from django.db import transaction, connections
from django.utils import timezone

from core.models import AIProvider, Scene, Mailbox


class Command(BaseCommand):
    help = "Migrate AIProvider (dependency), Scene and Mailbox data from legacy SQLite to the default database (PostgreSQL)."

    def add_arguments(self, parser):
        parser.add_argument(
            '--models',
            type=str,
            default='AIProvider,Scene,Mailbox',
            help='Comma-separated model names to migrate (supports: AIProvider, Scene, Mailbox). AIProvider is recommended when migrating Scene due to FK.'
        )
        parser.add_argument(
            '--dry-run',
            action='store_true',
            help='Preview counts and planned actions without writing to the default database.'
        )
        parser.add_argument(
            '--purge',
            action='store_true',
            help='Purge target tables in default database before importing (DANGEROUS).'
        )
        parser.add_argument(
            '--reset-seq',
            action='store_true',
            help='After import, reset PostgreSQL sequences for the migrated tables to max(id).'
        )

    def handle(self, *args, **options):
        if 'legacy' not in settings.DATABASES:
            self.stderr.write(self.style.ERROR("No 'legacy' database configured. Set LEGACY_SQLITE_PATH in environment."))
            return

        targets = [s.strip() for s in (options.get('models') or '').split(',') if s.strip()]
        dry_run = options.get('dry_run', False)
        purge = options.get('purge', False)
        reset_seq = options.get('reset_seq', False)

        # Normalize order: ensure AIProvider first if included and Scene requested
        order = []
        for name in ('AIProvider', 'Scene', 'Mailbox'):
            if name in targets:
                order.append(name)
        if not order:
            self.stdout.write(self.style.WARNING('No valid models specified; nothing to do.'))
            return

        self.stdout.write(self.style.NOTICE(f"Using legacy DB alias: 'legacy', default DB: '{settings.DATABASES.get('default', {}).get('ENGINE', '')}'"))

        if purge and not dry_run:
            self._purge(order)

        summary = {}
        try:
            with transaction.atomic(using='default'):
                if 'AIProvider' in order:
                    summary['AIProvider'] = self._migrate_ai_provider(dry_run)
                if 'Scene' in order:
                    summary['Scene'] = self._migrate_scene(dry_run)
                if 'Mailbox' in order:
                    summary['Mailbox'] = self._migrate_mailbox(dry_run)
                # If dry_run, force rollback by raising in-transaction exception after summary built
                if dry_run:
                    raise RuntimeError('Dry run requested; rolling back changes.')
        except RuntimeError as e:
            if dry_run and 'Dry run requested' in str(e):
                self.stdout.write(self.style.WARNING('Dry-run completed; no changes committed.'))
            else:
                raise

        # Optionally reset sequences on PostgreSQL
        if reset_seq and not dry_run:
            self._reset_sequences(['core_aiprovider', 'core_scene', 'core_mailbox'])

        # Print summary
        self.stdout.write('')
        self.stdout.write(self.style.SUCCESS('Migration Summary'))
        for k, v in summary.items():
            self.stdout.write(f"- {k}: {v}")

    def _purge(self, order):
        # Purge order should respect FKs: Scene depends on AIProvider, so purge Scene before AIProvider
        purge_order = []
        if 'Scene' in order:
            purge_order.append('Scene')
        if 'Mailbox' in order:
            purge_order.append('Mailbox')
        if 'AIProvider' in order:
            purge_order.append('AIProvider')
        for name in purge_order:
            if name == 'Scene':
                Scene.objects.using('default').all().delete()
                self.stdout.write(self.style.WARNING('Purged default.Scene'))
            elif name == 'Mailbox':
                Mailbox.objects.using('default').all().delete()
                self.stdout.write(self.style.WARNING('Purged default.Mailbox'))
            elif name == 'AIProvider':
                AIProvider.objects.using('default').all().delete()
                self.stdout.write(self.style.WARNING('Purged default.AIProvider'))

    def _copy_local_fields(self, src_obj, exclude_fields=None):
        data = {}
        exclude_fields = set(exclude_fields or [])
        for f in src_obj._meta.local_fields:
            if f.name in exclude_fields:
                continue
            # Skip relation fields (ForeignKey are still local_fields); we will handle them explicitly
            if f.is_relation:
                continue
            data[f.name] = getattr(src_obj, f.attname if hasattr(f, 'attname') else f.name)
        return data

    def _migrate_ai_provider(self, dry_run=False):
        src_qs = AIProvider.objects.using('legacy').all().order_by('id')
        created = updated = 0
        for src in src_qs:
            defaults = self._copy_local_fields(src, exclude_fields={'id'})
            lookup = {'name': getattr(src, 'name', None)}
            if not lookup['name']:
                continue
            if dry_run:
                if AIProvider.objects.using('default').filter(**lookup).exists():
                    updated += 1
                else:
                    created += 1
            else:
                obj, was_created = AIProvider.objects.using('default').update_or_create(
                    **lookup, defaults=defaults
                )
                created += 1 if was_created else 0
                updated += 0 if was_created else 1
        return f"from legacy: {src_qs.count()}, created: {created}, updated: {updated}"

    def _migrate_scene(self, dry_run=False):
        src_qs = Scene.objects.using('legacy').all().order_by('id')
        created = updated = skipped = 0
        for src in src_qs:
            provider = None
            try:
                src_provider = getattr(src, 'ai_provider', None)
                provider_name = getattr(src_provider, 'name', None)
                if provider_name:
                    provider = AIProvider.objects.using('default').filter(name=provider_name).first()
                    if provider is None and src_provider is not None:
                        prov_defaults = self._copy_local_fields(src_provider, exclude_fields={'id'})
                        provider, _ = AIProvider.objects.using('default').update_or_create(
                            name=provider_name, defaults=prov_defaults
                        )
            except Exception:
                provider = None
            defaults = self._copy_local_fields(src, exclude_fields={'id'})
            defaults.pop('ai_provider_id', None)
            if provider is not None:
                defaults['ai_provider'] = provider
            else:
                skipped += 1
                continue
            lookup = {'name': getattr(src, 'name', None)}
            if not lookup['name']:
                skipped += 1
                continue
            if dry_run:
                if Scene.objects.using('default').filter(**lookup).exists():
                    updated += 1
                else:
                    created += 1
            else:
                obj = Scene.objects.using('default').filter(**lookup).first()
                if obj:
                    for k, v in defaults.items():
                        setattr(obj, k, v)
                    obj.save(using='default')
                    updated += 1
                else:
                    obj = Scene.objects.using('default').create(**defaults)
                    created += 1
        total = src_qs.count()
        return f"from legacy: {total}, created: {created}, updated: {updated}, skipped(no provider/name): {skipped}"

    def _migrate_mailbox(self, dry_run=False):
        src_qs = Mailbox.objects.using('legacy').all().order_by('id')
        created = updated = 0
        for src in src_qs:
            defaults = self._copy_local_fields(src, exclude_fields={'id'})
            # Use name as natural key (assumed unique per models)
            lookup = {'name': getattr(src, 'name', None)}
            if not lookup['name']:
                continue
            if dry_run:
                if Mailbox.objects.using('default').filter(**lookup).exists():
                    updated += 1
                else:
                    created += 1
            else:
                obj = Mailbox.objects.using('default').filter(**lookup).first()
                if obj:
                    for k, v in defaults.items():
                        setattr(obj, k, v)
                    obj.save(using='default')
                    updated += 1
                else:
                    Mailbox.objects.using('default').create(**defaults)
                    created += 1
        total = src_qs.count()
        return f"from legacy: {total}, created: {created}, updated: {updated}"

    def _reset_sequences(self, table_names):
        default_db = connections['default']
        vendor = default_db.vendor
        if vendor != 'postgresql':
            self.stdout.write(self.style.WARNING('Sequence reset skipped: default DB is not PostgreSQL.'))
            return
        with default_db.cursor() as cursor:
            for table in table_names:
                try:
                    cursor.execute(
                        f"SELECT setval(pg_get_serial_sequence('%s','id'), COALESCE(MAX(id), 1), MAX(id) IS NOT NULL) FROM %s;" % (table, table)
                    )
                    self.stdout.write(self.style.SUCCESS(f'Reset sequence for {table}'))
                except Exception as e:
                    self.stdout.write(self.style.ERROR(f'Failed to reset sequence for {table}: {e}'))